﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
   public class DBHelper5
    {
       public DBHelper5()
       {
       }
        /// <summary>
        /// 连接字符串
        /// </summary>
      // private static string connectionString = ConfigurationManager.ConnectionStrings["MySQLConnString"].ConnectionString;
        private static string connectionString = ConfigurationManager.ConnectionStrings["sql5"].ConnectionString;        /// <summary>
        /// 执行查询语句返回首行首列
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>查询结果首行首列</returns>
        public static object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, null, CommandType.Text);
        }
        /// <summary>
        /// 执行查询语句返回首行首列
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="paras">用于查询语句的参数数组</param>
        /// <returns>查询结果首行首列</returns>
        public static object ExecuteScalar(string sql, SqlParameter[] paras)
        {
            return ExecuteScalar(sql, paras, CommandType.Text);
        }
        /// <summary>
        /// 执行查询语句返回首行首列
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="paras">用于查询语句的参数数组</param>
        /// <param name="type">执行语句的类型</param>
        /// <returns>查询结果首行首列</returns>
        public static object ExecuteScalar(string sql, SqlParameter[] paras, CommandType type)
        {
            object result = null;
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                try
                {
                    con.Open();
                    SqlCommand cmd = CreateCommand(sql, paras, con, type);
                    result = cmd.ExecuteScalar();
                }
                catch (Exception ex)
                {

                    Console.WriteLine(ex.Message);
                }

            }
            return result;
        }
        /// <summary>
        /// 执行查询语句返回所影响的行数
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>查询结果所影响的行数</returns>
        public static int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, null, CommandType.Text);
        }
        /// <summary>
        /// 执行查询语句返回所影响的行数
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="paras">用于查询语句的参数数组</param>
        ///  <returns>查询结果所影响的行数</returns>
        public static int ExecuteNonQuery(string sql, SqlParameter[] paras)
        {
            return ExecuteNonQuery(sql, paras, CommandType.Text);
        }
        /// <summary>
        /// 执行查询语句返回所影响的行数
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="paras">用于查询语句的参数数组</param>
        /// <param name="type">执行语句的类型</param>
        ///  <returns>查询结果所影响的行数</returns>
        public static int ExecuteNonQuery(string sql, SqlParameter[] paras, CommandType type)
        {
            int result = 0;
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                try
                {
                    con.Open();
                    SqlCommand cmd = CreateCommand(sql, paras, con, type);
                    result = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {

                    Console.WriteLine(ex.Message);
                }
            }
            return result;
        }

        /// <summary>
        /// 执行查询语句返回一个存放数据集的SqlDataReader对象
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>存放数据集的SqlDataReader对象</returns>
        public static SqlDataReader ExecuteReader(string sql)
        {
            return ExecuteReader(sql, null, CommandType.Text);
        }
        /// <summary>
        /// 执行查询语句返回一个存放数据集的SqlDataReader对象
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="paras">用于查询语句的参数数组</param>
        /// <returns>存放数据集的SqlDataReader对象</returns>
        public static SqlDataReader ExecuteReader(string sql, SqlParameter[] paras)
        {
            return ExecuteReader(sql, paras, CommandType.Text);
        }
        /// <summary>
        /// 执行查询语句返回一个存放数据集的SqlDataReader对象
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="paras">用于查询语句的参数数组</param>
        /// <param name="type">执行语句的类型</param>
        /// <returns>存放数据集的SqlDataReader对象</returns>
        public static SqlDataReader ExecuteReader(string sql, SqlParameter[] paras, CommandType type)
        {
            SqlDataReader result = null;
            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                con.Open();
                SqlCommand cmd = CreateCommand(sql, paras, con, type);
                result = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message);
            }


            return result;
        }
        /// <summary>
        /// 执行多条查询语句，返回DataSet
        /// </summary>
        /// <param name="ArrayList">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(ArrayList al)
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                try
                {
                    con.Open();
                    for (int i = 0; i < al.Count; i++)
                    {
                        SqlDataAdapter command = new SqlDataAdapter(al[i].ToString(), con);
                        command.Fill(ds, "ds" + i);
                    }
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }

    /// <summary>
    /// 分页查询函数
    /// </summary>
    /// <param name="connStr">数据库连接字符串</param>
    /// <param name="strSql">sql语句</param>
    /// <param name="Params">参数</param>
    /// <param name="pageSize">每页条数</param>
    /// <param name="pageIndex">页码</param>
    /// <param name="order">排序字段</param>
    /// <param name="sort">排序放</param>
    /// <returns>备注：查询效率高，但是会建临时表</returns>
    public static DataTable Pagination2(string strSql, int pageSize, int pageIndex, string order, string sort)
    {
        SqlConnection conn = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter ada = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        cmd.Connection = conn;
        string sqlFinal = string.Format(@"SELECT ROW_NUMBER() OVER(ORDER BY {1} {2})
                   AS rownum,* into #tmp2 from ({0}) tmp1;
                   select  (select count(*) from #tmp2)total,* from #tmp2 where #tmp2.rownum BETWEEN
                   @startRow and @endRow", strSql, order, sort);
        List<SqlParameter> Params = new List<SqlParameter>();
        Params.Add(new SqlParameter("@startRow", (pageIndex - 1) * pageSize + 1) { SqlDbType = SqlDbType.Int });
        Params.Add(new SqlParameter("@endRow", pageIndex * pageSize) { SqlDbType = SqlDbType.Int });
        cmd.CommandText = sqlFinal;
        for (int i = 0; i < Params.Count; i++)
        {
            cmd.Parameters.Add(Params[i]);
        }
        ada.Fill(dt);
        return dt;
    }
    /// <summary>
    /// 执行查询语句返回一个存放数据的DataTable对象
    /// </summary>
    /// <param name="sql">查询语句</param>
    /// <returns>存放数据的DataTable对象</returns>
    public static DataTable ExecuteDataTable(string sql)
        {
            return ExecuteDataTable(sql, null, CommandType.Text);
        }
        /// <summary>
        /// 执行查询语句返回一个存放数据的DataTable对象
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="paras">用于查询语句的参数数组</param>
        /// <returns>存放数据的DataTable对象</returns>
        public static DataTable ExecuteDataTable(string sql, SqlParameter[] paras)
        {
            return ExecuteDataTable(sql, paras, CommandType.Text);
        }
        /// <summary>
        /// 执行查询语句返回一个存放数据的DataTable对象
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="paras">用于查询语句的参数数组</param>
        /// <param name="type">执行语句的类型</param>
        /// <returns>存放数据的DataTable对象</returns>
        public static DataTable ExecuteDataTable(string sql, SqlParameter[] paras, CommandType type)
        {
            DataTable result = new DataTable();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                try
                {
                    con.Open();
                    SqlCommand cmd = CreateCommand(sql, paras, con, type);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(result);
                }
                catch (Exception ex)
                {

                    Console.WriteLine(ex.Message);
                }
            }
            return result;
        }
        /// <summary>
        /// 执行查询语句返回一个存放数据的DataTable对象
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>存放数据的DataTable对象</returns>
        public static DataSet ExecuteDataSet(string sql)
        {
            return ExecuteDataSet(sql, null, CommandType.Text);
        }
        /// <summary>
        /// 执行查询语句返回一个存放数据的DataTable对象
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="paras">用于查询语句的参数数组</param>
        /// <returns>存放数据的DataTable对象</returns>
        public static DataSet ExecuteDataSet(string sql, SqlParameter[] paras)
        {
            return ExecuteDataSet(sql, paras, CommandType.Text);
        }
        /// <summary>
        /// 执行查询语句返回一个存放数据的DataTable对象
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="paras">用于查询语句的参数数组</param>
        /// <param name="type">执行语句的类型</param>
        /// <returns>存放数据的DataTable对象</returns>
        public static DataSet ExecuteDataSet(string sql, SqlParameter[] paras, CommandType type)
        {
            DataSet result = new DataSet();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                try
                {
                    con.Open();
                    SqlCommand cmd = CreateCommand(sql, paras, con, type);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(result);
                }
                catch (Exception ex)
                {

                    Console.WriteLine(ex.Message);
                }
            }
            return result;
        }
        /// <summary>
        /// 构造一个命令对象
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="paras">用于查询语句的参数数组</param>
        /// <param name="con">连接对象</param>
        /// <returns>命令对象</returns>
        private static SqlCommand CreateCommand(string sql, SqlParameter[] paras, SqlConnection con, CommandType type)
        {
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType = type;
            if (paras != null)
            {
                cmd.Parameters.AddRange(paras);
            }
            return cmd;
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
        public static int ExecuteSqlTran(List<String> SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的数据字典（key为sql语句，value是该语句的OracleParameter[]）</param>
        public static int ExecuteSqlTran(Dictionary<StringBuilder, SqlParameter[]> SQLStringList)
        {
            int rvalue = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        //循环
                        foreach (var myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            rvalue += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return rvalue;
        }
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {


                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

    }

	
